More Excel questions

"mr_gofast" (jake_berenshteyn)
07/23/2014 at 14:05 • Filed to: EXCEL

Hey guys - as usual im stumped in Excel - i have my worksheet with data on it sorted by Region - each reocrd within a region has a subdivision its relegated to - how do i have a cell which scans the column with my regions, finds the one i designate and lists the subs in that region

i have 5 inspections completed for ATL region

place A

how do i list those 5 places in another cell - so i can see what places were inspected for a given region?

DISCUSSION (14)

RamblinRover Luxury-Yacht > mr_gofast
07/23/2014 at 14:11

If you want all five cells' results in the same cell, you're going to have to concatenate them. If your categories vary in length, you may have to do something fancy - if they're all the same, you can make a fixed selection for each output cell.

bob and john > mr_gofast
07/23/2014 at 14:11

it seems to me like a LOT of this stuff could/should be done in another program that scan matrices and can count it.

RamblinRover Luxury-Yacht > bob and john
07/23/2014 at 14:15

The best way, bar none, would be in Access, but setting up custom VB and GUI elements to make it fancy and professional would be a TON of work.

mr_gofast > RamblinRover Luxury-Yacht
07/23/2014 at 14:16

i have 5 regions in total - i just want to be able to have a cell that lists the subs inspected in each region.

mr_gofast > bob and john
07/23/2014 at 14:18

i wish...this be Fed gov - so were cheap and bnusted ass - hence the Excel.

bob and john > RamblinRover Luxury-Yacht
07/23/2014 at 14:18

yea, but it also SAVES you a tone of work down the line.

and you can tell your boss you deserve a raise.

bob and john > mr_gofast
07/23/2014 at 14:20

oh, so dont worry if you have to take forever to it anyways, its not like the gov does anything useless #shotsfired #plsunderstandmysarcasm

RamblinRover Luxury-Yacht > mr_gofast
07/23/2014 at 14:24

I'm still not 100% sure what you're trying to do, so my ability to help is limited. Partly, it's a question of "what data will be changing"?

If you want to just have a cell per region on a second page that lists inspections as somebody adds them to a list, that's very doable. If you want to be able to select from a drop-down list one of your regions and have a cell beneath that list your inspections completed, that's doable as well, if harder. If your locations to be inspected are fixed, but you want a box next to each that can be changed to yes/no for whether it's inspected, then another box to list those that have been checked off, that can be done.

I need a little more guidance on how to help you.

KusabiSensei - Captain of the Toronto Maple Leafs > mr_gofast
07/23/2014 at 14:24

VLOOKUP()s, yo.

EDIT: More specifically, you will want your data that you will pull in the column next to the key being referenced by the VLOOKUP() and then returning that data.

In effect, what you are doing is building a relational database in Excel. It's not speedy to do, but it can be done.

I've used that with comparing multiple sets of data across different sheets before.

mr_gofast > bob and john
07/23/2014 at 14:24

lol i do.

mr_gofast > RamblinRover Luxury-Yacht
07/23/2014 at 14:25

im not allowed to use Access yet..

RamblinRover Luxury-Yacht > bob and john
07/23/2014 at 14:25

True, but we're still at the "needs to learn how to concatenate" step. Baby steps.

mr_gofast > RamblinRover Luxury-Yacht
07/23/2014 at 14:26

my boss wants to be able to see a table with each region listed and under each region he wants a list of the subdivisions that were in fact inspected.

RamblinRover Luxury-Yacht > mr_gofast
07/23/2014 at 14:36

Okay, so you'd build the table, list each region, and then have cells under each region, one per subdivision, that you want to be a list of less than 10 locations in that subdivision?

If those locations are going to change each time, or at least get typed in as they get inspected, you will set aside a set of cells on one of your pages for each subdivision to type the locations in. Your locations cell for that subdivision will then "concatenate" the contents of those cells to itself. If those cells are empty, a quick&dirty function will just import blanks, so you'll have "location a, location b, , , , " or something like that with a string of commas. To clean that up needs the IF function.

If the locations just need to be checked off/have "y" or "n" or another note that they've been checked, then you have a set of cells for each subdivision, but you modify the concatenate command with "IF" statements based on, say, whether there's a "y" in the adjacent box. If it encounters a y, it will put the contents of the cell in the list, otherwise nothing. This can be customized to even offer two lists for each subdivision, an inspected list and an uninspected list. To keep people from breaking it, you can even make the "Inspected?" cell a drop-down box to limit what people can put in - just a y or n.